﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;


public partial class UC_ucNH_BB : System.Web.UI.UserControl
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    NpoiExport cl = new NpoiExport();
    string frmName = "frmBaoCaoNhapHang";
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0, f_Khoa = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static double klmuaVAT = 0, klmuaKVAT = 0, klthucte = 0, tlhang = 0, klsauquidoi = 0, giavlvat = 0, giavlkhongvat = 0, ttvlcovat = 0, ttvlkvat = 0, cvccovat = 0, cvckvt = 0, ttcvccovat = 0, ttcvckvat = 0, tt1m3covat = 0, tt1m3kvat = 0, tt1chuyencovat = 0, tt1chuyenkvat = 0;
    static string lvl = "";
    static System.Data.DataTable dt;
    System.Data.DataTable dtPrint;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa,Khoa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
                f_Khoa = int.Parse(dt2.Rows[0]["Khoa"].ToString());
            }

            CurrentPage = 1;
            for (int i = 1; i <= 12; i++)
            {
                dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
            }
            dlThang.Items.Insert(0, new ListItem("Theo thời gian", ""));
            dlThang.SelectedIndex = 0;

            int year1 = DateTime.Now.Year - 3;
            int year2 = DateTime.Now.Year;
            for (int i = year2; i >= year1; i--)
            {
                dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
            }
            dlNam.SelectedValue = year2.ToString();

            txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            dlNam.Visible = false;

            LoadbenChayCuoc(dlBenChayCuoc);
            LoadNhaCungCap(dlBenBan, "--Chọn bên bán--");
            LoadTramTron(dlTramTron);
            //lbTrangThai.Visible = false;
            //btnKhoa.Visible = false;
        }
    }
    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "BCNH-BenBan";

            if (dlBenBan.SelectedIndex > 0)
                sheetname += "-" + dlBenBan.SelectedItem.Text;

            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;

            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            string benban = dlBenBan.SelectedIndex > 0 ? "Bên bán: " + dlBenBan.SelectedItem.Text : "";
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BÁO CÁO SẢN LƯỢNG NHẬP " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 12);
            sheet.AddMergedRegion(cra);

            //BENBAN
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue(benban);
            r1c1.CellStyle = hs3;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(3, 3, 0, 12);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion

            #region Header1-2

            //header2
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Giá vật liệu", "Thành tiền vật liệu", "Giá cước VC", "TT cước VC", "Thành tiền hàng/1M3", "Thành tiền hàng/1 chuyến" };
            int[] width = { 12, 14, 16, 18, 20, 22 };

            var cell = row.CreateCell(7);
            cell.SetCellValue("Khối lượng mua");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;

            cra = new CellRangeAddress(4, 4, 7, 8);
            sheet.AddMergedRegion(cra);


            //tao tieu de cot
            for (int j = 0; j <= 5; j++)
            {
                cell = row.CreateCell(width[j]);
                cell.SetCellValue(header[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
                cra = new CellRangeAddress(4, 4, width[j], width[j] + 1);
                sheet.AddMergedRegion(cra);
            }
            //fix lai border
            for (int h = 0; h <= 23; h++)
            {
                if (h != 7 && h != 12 && h != 14 && h != 16 && h != 18 && h != 20 && h != 22)
                {
                    r1c1 = row.CreateCell(h);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }

            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header2 = { "Số phiếu", "Ngày nhập", "Loại vật liệu", "Bên bán", "Bên chạy cước", "Biển số xe", "Có VAT", "Không VAT", "KL thực tế", "TL hàng", "TL sau qui đổi", "Có VAT", "Không VAT", "Có VAT", "Không VAT", "Có VAT", "Không VAT", "Có VAT", "Không VAT", "Có VAT", "Không VAT", "Có VAT", "Không VAT" };

            cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header2.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header2[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }
            #endregion
            //End header-----------------------------------------------------------------------

            //ghi du lieu tung dong
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j <= dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    if (j >= 7)
                    {
                        if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                        }
                        else
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                        cell.CellStyle = hsRight;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                        if (j <= 2)
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }

            }
            for (int ik = 0; ik < dt.Columns.Count; ik++)
            {
                sheet.AutoSizeColumn(ik);
            }
            #region Footer

            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(5);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            string[] footer = { "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X" };
            for (int k = 7; k <= 23; k++)
            {

                if (k != 10 && k != 11 && k != 12 && k != 13 && k != 16 && k != 17)
                {
                    cell = row.CreateCell(k);
                    cell.CellFormula = "Sum(" + footer[k - 7] + "7:" + footer[k - 7] + rowIndex.ToString() + ")";
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;
                }

            }
            //fix lai border
            for (int k = 0; k <= 23; k++)
            {
                if ((k != 5 && k <= 4) || k == 6 || k == 10 || k == 11 || k == 12 || k == 13 || k == 16 || k == 17)
                {
                    r1c1 = row.CreateCell(k);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }

            #endregion

            //end sheet tong hop--------------------------

            //print tung nha cung cap
            #region NCC
            if (dlBenBan.SelectedIndex.Equals(0))
            {
                int STT = 0; string bb = "";
                //ghi sheet con------------------------------------------
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        bb = dt.Rows[i]["BenBan"].ToString();
                        if (!bb.Equals(dt.Rows[i - 1]["BenBan"].ToString()))
                        {
                            STT = 0;
                            //-----------Footer
                            #region Footer
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(5);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            for (int k = 7; k <= 23; k++)
                            {

                                if (k != 10 && k != 11 && k != 12 && k != 13 && k != 16 && k != 17)
                                {
                                    cell = row.CreateCell(k);
                                    cell.CellFormula = "Sum(" + footer[k - 7] + "7:" + footer[k - 7] + rowIndex.ToString() + ")";
                                    cell.Row.Height = 500;
                                    cell.CellStyle = hs4;
                                }

                            }
                            //fix lai border
                            for (int k = 0; k <= 23; k++)
                            {
                                if ((k != 5 && k <= 4) || k == 6 || k == 10 || k == 11 || k == 12 || k == 13 || k == 16 || k == 17)
                                {
                                    r1c1 = row.CreateCell(k);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }

                            #endregion
                            for (int ik = 0; ik < dt.Columns.Count; ik++)
                            {
                                sheet.AutoSizeColumn(ik);
                            }
                            sheet = workbook.CreateSheet(bb.Trim());
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO SẢN LƯỢNG NHẬP " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //BENBAN
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Bên bán: " + bb);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion

                            #region Header1-2

                            //header2
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(7);
                            cell.SetCellValue("Khối lượng mua");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;

                            cra = new CellRangeAddress(4, 4, 7, 8);
                            sheet.AddMergedRegion(cra);


                            //tao tieu de cot
                            for (int j = 0; j <= 5; j++)
                            {
                                cell = row.CreateCell(width[j]);
                                cell.SetCellValue(header[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                                cra = new CellRangeAddress(4, 4, width[j], width[j] + 1);
                                sheet.AddMergedRegion(cra);
                            }
                            //fix lai border
                            for (int h = 0; h <= 23; h++)
                            {
                                if (h != 7 && h != 12 && h != 14 && h != 16 && h != 18 && h != 20 && h != 22)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 0; j < header2.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header2[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                            //End header-----------------------------------------------------------------------

                            STT++;
                        }
                    }
                    else
                    {
                        bb = "";
                        if (!bb.Equals(dt.Rows[i]["BenBan"].ToString()))
                        {
                            bb = dt.Rows[i]["BenBan"].ToString();
                            sheet = workbook.CreateSheet(bb.Trim());
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO SẢN LƯỢNG NHẬP " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //BENBAN
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Bên bán: " + bb);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion

                            #region Header1-2

                            //header2
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(7);
                            cell.SetCellValue("Khối lượng mua");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;

                            cra = new CellRangeAddress(4, 4, 7, 8);
                            sheet.AddMergedRegion(cra);


                            //tao tieu de cot
                            for (int j = 0; j <= 5; j++)
                            {
                                cell = row.CreateCell(width[j]);
                                cell.SetCellValue(header[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                                cra = new CellRangeAddress(4, 4, width[j], width[j] + 1);
                                sheet.AddMergedRegion(cra);
                            }
                            //fix lai border
                            for (int h = 0; h <= 23; h++)
                            {
                                if (h != 7 && h != 12 && h != 14 && h != 16 && h != 18 && h != 20 && h != 22)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 0; j < header2.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header2[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                        }
                    }
                    #endregion

                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue((STT).ToString());
                    cell.CellStyle = hsCenter;

                    for (int j = 1; j <= dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);

                        if (j >= 7)
                        {
                            if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                            }
                            else
                                cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                            if (j <= 2)
                                cell.CellStyle = hsCenter;
                            else
                                cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;
                    }


                    if (i == dt.Rows.Count - 1)
                    {
                        #region Footer
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(5);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        for (int k = 7; k <= 23; k++)
                        {

                            if (k != 10 && k != 11 && k != 12 && k != 13 && k != 16 && k != 17)
                            {
                                cell = row.CreateCell(k);
                                cell.CellFormula = "Sum(" + footer[k - 7] + "7:" + footer[k - 7] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }

                        }
                        //fix lai border
                        for (int k = 0; k <= 23; k++)
                        {
                            if ((k != 5 && k <= 4) || k == 6 || k == 10 || k == 11 || k == 12 || k == 13 || k == 16 || k == 17)
                            {
                                r1c1 = row.CreateCell(k);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }
                        }
                        #endregion
                        for (int ik = 0; ik < dt.Columns.Count; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }


                }
            }
            #endregion
            else
                //print tung loai vl cua ncc do

                if (dlBenBan.SelectedIndex > 0)
                {
                    int STT = 0; string bb = "";
                    //ghi sheet con------------------------------------------
                    #region LVL
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        STT++;
                        #region Start
                        if (i > 0)
                        {
                            bb = dt.Rows[i]["LoaiVL"].ToString();
                            if (!bb.Equals(dt.Rows[i - 1]["LoaiVL"].ToString()))
                            {
                                STT = 0;
                                //-----------Footer
                                #region Footer
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(5);
                                cell.SetCellValue("Tổng cộng");
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;

                                for (int k = 7; k <= 15; k++)
                                {

                                    if (k != 10 && k != 11 && k != 12 && k != 13 && k != 16 && k != 17)
                                    {
                                        cell = row.CreateCell(k);
                                        cell.CellFormula = "Sum(" + footer[k - 7] + "7:" + footer[k - 7] + rowIndex.ToString() + ")";
                                        cell.Row.Height = 500;
                                        cell.CellStyle = hs4;
                                    }

                                }
                                //fix lai border
                                for (int k = 0; k <= 15; k++)
                                {
                                    if ((k != 5 && k <= 4) || k == 6 || k == 10 || k == 11 || k == 12 || k == 13 || k == 16 || k == 17)
                                    {
                                        r1c1 = row.CreateCell(k);
                                        r1c1.CellStyle = hs4;
                                        r1c1.Row.Height = 400;
                                    }
                                }

                                #endregion
                                for (int ik = 0; ik < dt.Columns.Count; ik++)
                                {
                                    sheet.AutoSizeColumn(ik);
                                }
                                sheet = workbook.CreateSheet(cl.EscapeSheetName(bb.Trim()));
                                rowIndex = 0;

                                //Start header-----------------------------------------------------------------------
                                #region Header
                                //tao hnag dau tien

                                //cong ty
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                                r1c1.CellStyle = hs1;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(0, 0, 0, 4);
                                sheet.AddMergedRegion(cra);

                                //chi nhanh
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                                r1c1.CellStyle = hs1;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(1, 1, 0, 4);
                                sheet.AddMergedRegion(cra);

                                //tieu de bao cao

                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("BÁO CÁO SẢN LƯỢNG NHẬP " + ngaythang);
                                r1c1.CellStyle = hs2;
                                r1c1.Row.Height = 500;

                                cra = new CellRangeAddress(2, 2, 0, 12);
                                sheet.AddMergedRegion(cra);

                                //BENBAN
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Loại vật liệu: " + bb);
                                r1c1.CellStyle = hs3;
                                r1c1.Row.Height = 500;

                                cra = new CellRangeAddress(3, 3, 0, 12);
                                sheet.AddMergedRegion(cra);

                                //freeze panes
                                sheet.CreateFreezePane(0, 6);
                                #endregion

                                #region Header1-2

                                //header2
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(7);
                                cell.SetCellValue("Khối lượng mua");
                                cell.CellStyle = hs4;
                                cell.Row.Height = 400;

                                cra = new CellRangeAddress(4, 4, 7, 8);
                                sheet.AddMergedRegion(cra);


                                //tao tieu de cot
                                for (int j = 0; j <= 1; j++)
                                {
                                    cell = row.CreateCell(width[j]);
                                    cell.SetCellValue(header[j].ToString());
                                    cell.CellStyle = hs4;
                                    cell.Row.Height = 500;
                                    cra = new CellRangeAddress(4, 4, width[j], width[j] + 1);
                                    sheet.AddMergedRegion(cra);
                                }
                                //fix lai border
                                for (int h = 0; h <= 15; h++)
                                {
                                    if (h != 7 && h != 12 && h != 14 && h != 16 && h != 18 && h != 20 && h != 22)
                                    {
                                        r1c1 = row.CreateCell(h);
                                        r1c1.CellStyle = hs4;
                                        r1c1.Row.Height = 400;
                                    }
                                }

                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(0);
                                cell.SetCellValue("STT");
                                cell.CellStyle = hs4;
                                cell.Row.Height = 400;


                                //tao tieu de cot
                                for (int j = 0; j < 15; j++)
                                {
                                    cell = row.CreateCell(j + 1);
                                    cell.SetCellValue(header2[j].ToString());
                                    cell.CellStyle = hs4;
                                    cell.Row.Height = 500;
                                }
                                #endregion
                                //End header-----------------------------------------------------------------------

                                STT++;
                            }
                        }
                        else
                        {
                            bb = "";
                            if (!bb.Equals(dt.Rows[i]["LoaiVL"].ToString()))
                            {
                                bb = dt.Rows[i]["LoaiVL"].ToString();
                                sheet = workbook.CreateSheet(cl.EscapeSheetName(bb.Trim()));
                                rowIndex = 0;

                                //Start header-----------------------------------------------------------------------
                                #region Header
                                //tao hnag dau tien

                                //cong ty
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                                r1c1.CellStyle = hs1;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(0, 0, 0, 4);
                                sheet.AddMergedRegion(cra);

                                //chi nhanh
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                                r1c1.CellStyle = hs1;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(1, 1, 0, 4);
                                sheet.AddMergedRegion(cra);

                                //tieu de bao cao

                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("BÁO CÁO SẢN LƯỢNG NHẬP " + ngaythang);
                                r1c1.CellStyle = hs2;
                                r1c1.Row.Height = 500;

                                cra = new CellRangeAddress(2, 2, 0, 12);
                                sheet.AddMergedRegion(cra);

                                //BENBAN
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Loại vật liệu: " + bb);
                                r1c1.CellStyle = hs3;
                                r1c1.Row.Height = 500;

                                cra = new CellRangeAddress(3, 3, 0, 12);
                                sheet.AddMergedRegion(cra);

                                //freeze panes
                                sheet.CreateFreezePane(0, 6);
                                #endregion

                                #region Header1-2

                                //header2
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(7);
                                cell.SetCellValue("Khối lượng mua");
                                cell.CellStyle = hs4;
                                cell.Row.Height = 400;

                                cra = new CellRangeAddress(4, 4, 7, 8);
                                sheet.AddMergedRegion(cra);


                                //tao tieu de cot
                                for (int j = 0; j <= 1; j++)
                                {
                                    cell = row.CreateCell(width[j]);
                                    cell.SetCellValue(header[j].ToString());
                                    cell.CellStyle = hs4;
                                    cell.Row.Height = 500;
                                    cra = new CellRangeAddress(4, 4, width[j], width[j] + 1);
                                    sheet.AddMergedRegion(cra);
                                }
                                //fix lai border
                                for (int h = 0; h <= 15; h++)
                                {
                                    if (h != 7 && h != 12 && h != 14 && h != 16 && h != 18 && h != 20 && h != 22)
                                    {
                                        r1c1 = row.CreateCell(h);
                                        r1c1.CellStyle = hs4;
                                        r1c1.Row.Height = 400;
                                    }
                                }

                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(0);
                                cell.SetCellValue("STT");
                                cell.CellStyle = hs4;
                                cell.Row.Height = 400;


                                //tao tieu de cot
                                for (int j = 0; j <= 14; j++)
                                {
                                    cell = row.CreateCell(j + 1);
                                    cell.SetCellValue(header2[j].ToString());
                                    cell.CellStyle = hs4;
                                    cell.Row.Height = 500;
                                }
                                #endregion
                            }
                        }
                        #endregion

                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(0);
                        cell.SetCellValue((STT).ToString());
                        cell.CellStyle = hsCenter;

                        for (int j = 1; j <= 15; j++)
                        {
                            cell = row.CreateCell(j);

                            if (j >= 7)
                            {
                                if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                                {
                                    cell.SetCellType(CellType.NUMERIC);
                                    cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                                }
                                else
                                    cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                                cell.CellStyle = hsRight;
                            }
                            else
                            {
                                cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                                if (j <= 2)
                                    cell.CellStyle = hsCenter;
                                else
                                    cell.CellStyle = headerLabelCellStyle;
                            }
                            cell.Row.Height = 500;
                        }


                        if (i == dt.Rows.Count - 1)
                        {
                            #region Footer
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(5);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            for (int k = 7; k <= 15; k++)
                            {

                                if (k != 10 && k != 11 && k != 12 && k != 13 && k != 16 && k != 17)
                                {
                                    cell = row.CreateCell(k);
                                    cell.CellFormula = "Sum(" + footer[k - 7] + "7:" + footer[k - 7] + rowIndex.ToString() + ")";
                                    cell.Row.Height = 500;
                                    cell.CellStyle = hs4;
                                }

                            }
                            //fix lai border
                            for (int k = 0; k <= 15; k++)
                            {
                                if ((k != 5 && k <= 4) || k == 6 || k == 10 || k == 11 || k == 12 || k == 13 || k == 16 || k == 17)
                                {
                                    r1c1 = row.CreateCell(k);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                            #endregion
                            for (int ik = 0; ik < dt.Columns.Count; ik++)
                            {
                                sheet.AutoSizeColumn(ik);
                            }
                        }


                    }
                    #endregion
                    //Load tong cong no

                    p = new SqlParameter[3];
                    p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
                    p[2].SqlDbType = SqlDbType.UniqueIdentifier;

                    dtPrint = ex.GetData("sp_BCNH_BB_ByBenBan_TongHopCongNo", p);
                    if (dtPrint.Rows.Count > 0)
                    {
                        sheet = workbook.CreateSheet("Tổng hợp công nợ");
                        rowIndex = 0;
                        #region Header
                        //tao hnag dau tien

                        //cong ty
                        row = sheet.CreateRow(rowIndex);
                        r1c1 = row.CreateCell(0);
                        r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                        r1c1.CellStyle = hs1;
                        r1c1.Row.Height = 400;

                        cra = new CellRangeAddress(0, 0, 0, 4);
                        sheet.AddMergedRegion(cra);

                        //chi nhanh
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);
                        r1c1 = row.CreateCell(0);
                        r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                        r1c1.CellStyle = hs1;
                        r1c1.Row.Height = 400;

                        cra = new CellRangeAddress(1, 1, 0, 4);
                        sheet.AddMergedRegion(cra);

                        //tieu de bao cao

                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);
                        r1c1 = row.CreateCell(0);
                        r1c1.SetCellValue("TỔNG HỢP CÔNG NỢ " + ngaythang);
                        r1c1.CellStyle = hs2;
                        r1c1.Row.Height = 500;

                        cra = new CellRangeAddress(2, 2, 0, 7);
                        sheet.AddMergedRegion(cra);

                        //BENBAN
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);
                        r1c1 = row.CreateCell(0);
                        r1c1.SetCellValue("Bên bán: " + dlBenBan.SelectedItem.Text);
                        r1c1.CellStyle = hs3;
                        r1c1.Row.Height = 500;

                        cra = new CellRangeAddress(3, 3, 0, 7);
                        sheet.AddMergedRegion(cra);

                        //freeze panes
                        sheet.CreateFreezePane(0, 6);
                        #endregion

                        #region Header1-2

                        //header2
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(1);
                        cell.SetCellValue("Khối lượng mua");
                        cell.CellStyle = hs4;
                        cell.Row.Height = 400;

                        cra = new CellRangeAddress(4, 4, 1, 2);
                        sheet.AddMergedRegion(cra);
                        //--------------
                        cell = row.CreateCell(3);
                        cell.SetCellValue("Đơn giá");
                        cell.CellStyle = hs4;
                        cell.Row.Height = 400;

                        cra = new CellRangeAddress(4, 4, 3, 4);
                        sheet.AddMergedRegion(cra);
                        //----------------
                        cell = row.CreateCell(5);
                        cell.SetCellValue("Thành tiền");
                        cell.CellStyle = hs4;
                        cell.Row.Height = 400;

                        cra = new CellRangeAddress(4, 4, 5, 6);
                        sheet.AddMergedRegion(cra);


                        //fix lai border
                        for (int h = 0; h <= 7; h++)
                        {
                            if (h == 7 || h % 2 == 0)
                            {
                                r1c1 = row.CreateCell(h);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }
                        }

                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        string[] td = { "Loại vật liệu", "Có VAT", "Không VAT", "Có VAT", "Không VAT", "Có VAT", "Không VAT", "Tổng tiền" };
                        //tao tieu de cot
                        for (int j = 0; j < td.Length; j++)
                        {
                            cell = row.CreateCell(j);
                            cell.SetCellValue(td[j].ToString());
                            cell.CellStyle = hs4;
                            cell.Row.Height = 500;
                        }
                        #endregion

                        for (int i = 0; i < dtPrint.Rows.Count; i++)
                        {
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            for (int j = 0; j < dtPrint.Columns.Count; j++)
                            {
                                cell = row.CreateCell(j);

                                if (j > 0)
                                {
                                    if (!dtPrint.Rows[i][j].ToString().Trim().Equals(""))
                                    {
                                        cell.SetCellType(CellType.NUMERIC);
                                        cell.SetCellValue(double.Parse(dtPrint.Rows[i][j].ToString()));
                                    }
                                    else
                                        cell.SetCellValue(dtPrint.Rows[i][j].ToString());
                                    cell.CellStyle = hsRight;
                                }
                                else
                                {
                                    cell.SetCellValue(dtPrint.Rows[i][j].ToString());
                                    cell.CellStyle = headerLabelCellStyle;
                                }
                                cell.Row.Height = 500;
                            }
                            cell = row.CreateCell(7);
                            cell.CellFormula = "Sum(F" + (rowIndex + 1).ToString() + ":G" + (rowIndex + 1).ToString() + ")";
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;


                        }
                        for (int ik = 0; ik < dtPrint.Columns.Count; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                        #region Footer

                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(0);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        string[] footer11 = { "B", "C", "D", "E", "F", "G", "H" };
                        for (int k = 1; k <= 7; k++)
                        {

                            if (k != 3 && k != 4)
                            {
                                cell = row.CreateCell(k);
                                cell.CellFormula = "Sum(" + footer11[k - 1] + "7:" + footer11[k - 1] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }
                            else
                            {
                                cell = row.CreateCell(k);
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }
                        }
                        int[] w = { 155, 64, 86, 69, 86, 71, 85, 91 };
                        for (int ik = 0; ik <= 7; ik++)
                        {
                            sheet.SetColumnWidth(ik, w[ik] * 36);
                        }
                        #endregion
                    }
                }
            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);
                string filename = dlBenBan.SelectedIndex > 0 ? dlBenBan.SelectedItem.Text : "";
                string saveAsFileName = "BCNH-BenBan-" + filename + " " + ngaythang + ".xls";

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }
        }
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        if (f_InBaoCao.Equals(1))
        {
            if (dlBenBan.SelectedIndex.Equals(0) && dlBenChayCuoc.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
            {
                SqlParameter[] p1 = new SqlParameter[2];
                p1[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p1[0].SqlDbType = SqlDbType.SmallDateTime;
                p1[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p1[1].SqlDbType = SqlDbType.SmallDateTime;

                dtPrint = ex.GetData("sp_BCNH_BB_TongHop_Print", p1);
            }
            else
                if (dlBenBan.SelectedIndex > 0 && dlBenChayCuoc.SelectedIndex.Equals(0) && dlTramTron.SelectedIndex.Equals(0))
                {
                    SqlParameter[] p1 = new SqlParameter[3];
                    p1[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                    p1[0].SqlDbType = SqlDbType.SmallDateTime;
                    p1[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                    p1[1].SqlDbType = SqlDbType.SmallDateTime;
                    p1[2] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
                    p1[2].SqlDbType = SqlDbType.UniqueIdentifier;

                    dtPrint = ex.GetData("sp_BCNH_BB_ByBenBan_Print", p1);
                }
                else
                    if (dlBenBan.SelectedIndex > 0 && dlBenChayCuoc.SelectedIndex > 0 && dlTramTron.SelectedIndex.Equals(0))
                    {
                        SqlParameter[] p1 = new SqlParameter[4];
                        p1[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                        p1[0].SqlDbType = SqlDbType.SmallDateTime;
                        p1[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                        p1[1].SqlDbType = SqlDbType.SmallDateTime;
                        p1[2] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
                        p1[2].SqlDbType = SqlDbType.UniqueIdentifier;
                        p1[3] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
                        p1[3].SqlDbType = SqlDbType.UniqueIdentifier;

                        dtPrint = ex.GetData("sp_BCNH_BB_ByBenBanBenChayCuoc_Print", p1);
                    }
            PrintNPOI(dtPrint);
        }
        else gstGetMess("Bạn không có quyền in báo cáo này", "");
    }
    protected void GV_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            GridView HeaderGrid = (GridView)sender;
            GridViewRow HeaderGridRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
            HeaderGridRow.Height = 20;
            HeaderGridRow.HorizontalAlign = HorizontalAlign.Center;
            TableCell HeaderCell = new TableCell();

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);


            HeaderCell = new TableCell();
            HeaderCell.Text = "KL mua";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;
            HeaderGridRow.Cells.Add(HeaderCell);


            HeaderCell = new TableCell();
            HeaderCell.Text = "Giá vật liệu";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền VL";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Giá cước vận chuyển";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền cước VC";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền /1m3";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);

            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền /1chuyến";
            HeaderCell.ColumnSpan = 2;
            HeaderGridRow.Cells.Add(HeaderCell);



            GV.Controls[0].Controls.AddAt(0, HeaderGridRow);
        }
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }

    void LoadbenChayCuoc(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn bên chạy cước--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadNhaCungCap(DropDownList dl, string title)
    {
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dl.Items.Clear();
        dl.DataSource = ex.GetData("sp_BCNH_LoadBenBanByTime", p);
        dl.DataBind();
        dl.Items.Insert(0, new ListItem(title, ""));
        dl.SelectedIndex = 0;
    }
    void LoadTramTron(DropDownList dl)
    {
        dlTramTron.Items.Clear();
        dlTramTron.DataSource = ex.GetData("sp_xdanhaphang_ListTramTron");
        dlTramTron.DataBind();
        dlTramTron.Items.Insert(0, new ListItem("--Chọn trạm trộn--", ""));
        dlTramTron.SelectedIndex = 0;
    }
    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            dlNam.Visible = false;
            //lbTrangThai.Visible = false;
            //btnKhoa.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
            //lbTrangThai.Visible = true;
            //btnKhoa.Visible = true;
        }

        getDate();

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dlBenBan.DataSource = ex.GetData("sp_BCNH_LoadBenBanByTime", p);
        dlBenBan.DataBind();
        dlBenBan.Items.Insert(0, new ListItem("--Chọn bên bán--", ""));
        dlBenBan.SelectedIndex = 0;

        LoadbenChayCuoc(dlBenChayCuoc);

        //if (dlThang.SelectedIndex > 0)
        //{
        //    //load trang thai khoa
        //    p = new SqlParameter[3];
        //    p[0] = new SqlParameter("@Name", "BCNH");
        //    p[0].SqlDbType = SqlDbType.NVarChar;
        //    p[1] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
        //    p[1].SqlDbType = SqlDbType.Int;
        //    p[2] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
        //    p[2].SqlDbType = SqlDbType.Int;

        //    string index = ex.GetValueString("sp_xdakhoa_Check", p);

        //    if (index.Trim().Equals("") || index.Trim().Equals("1"))
        //    {
        //        lbTrangThai.Text = "Trạng thái: Dữ liệu đang mở";
        //        btnKhoa.Text = "Khóa lại";
        //    }
        //    else
        //    {
        //        lbTrangThai.Text = "Trạng thái: Dữ liệu đang bị khóa";
        //        btnKhoa.Text = "Mở lại";
        //    }
        //}
    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }

    protected void btnXem_Click(object sender, EventArgs e)
    {
        if (f_SuDung.Equals(1))
        {
            CurrentPage = 1;
            btnNext.Enabled = true;

            if (dlBenBan.SelectedIndex > 0 && dlBenChayCuoc.SelectedIndex.Equals(0))
                SearchByBenBan(1);
            else if (dlBenBan.SelectedIndex > 0 && dlBenChayCuoc.SelectedIndex > 0)
                SearchByBenBanBenChayCuoc(1);
            else
                SearchAll(1);
        }
        else
            gstGetMess("Bạn không có quyền xem trang này", "Home.aspx");
    }
    private void SearchByBenBan(int page)
    {
        getDate();
        p = new SqlParameter[5];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@PageNumber", page);
        p[3].SqlDbType = SqlDbType.Int;
        p[4] = new SqlParameter("@RowspPage", PageNumber);
        p[4].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BCNH_BB_ByBenBan", p);
        GV.DataBind();

        p = new SqlParameter[3];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;

        dt = ex.GetData("sp_BCNH_BB_ByBenBan_GetFooter", p);
        LoadFooter();
    }
    private void SearchByBenBanBenChayCuoc(int page)
    {
        getDate();
        p = new SqlParameter[6];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@PageNumber", page);
        p[3].SqlDbType = SqlDbType.Int;
        p[4] = new SqlParameter("@RowspPage", PageNumber);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
        p[5].SqlDbType = SqlDbType.UniqueIdentifier;

        GV.DataSource = ex.GetData("sp_BCNH_BB_ByBenBanBenChayCuoc", p);
        GV.DataBind();

        p = new SqlParameter[4];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@BenChayCuoc", ex.GetGuid(dlBenChayCuoc.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;

        dt = ex.GetData("sp_BCNH_BB_ByBenBanBenChayCuoc_GetFooter", p);
        LoadFooter();
    }
    private void SearchAll(int page)
    {
        getDate();
        p = new SqlParameter[4];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        p[2] = new SqlParameter("@PageNumber", page);
        p[2].SqlDbType = SqlDbType.Int;
        p[3] = new SqlParameter("@RowspPage", PageNumber);
        p[3].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BCNH_BB_TongHop", p);
        GV.DataBind();

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;


        dt = ex.GetData("sp_BCNH_BB_TongHop_GetFooter", p);
        LoadFooter();
    }

    private void LoadFooter()
    {
        if (dt.Rows.Count > 0)
        {
            tlhang = double.Parse(dt.Rows[0]["TLHang"].ToString());
            klsauquidoi = double.Parse(dt.Rows[0]["KLSauQuiDoi"].ToString());
            klthucte = double.Parse(dt.Rows[0]["KLThucTe"].ToString());
            klmuaVAT = double.Parse(dt.Rows[0]["KLMuaVAT"].ToString());
            klmuaKVAT = double.Parse(dt.Rows[0]["KLMuaKoVAT"].ToString());
            //giavlvat = double.Parse(dt.Rows[0]["GiaVLCoVAT"].ToString());
            //giavlkhongvat = double.Parse(dt.Rows[0]["GiaVLKhongVAT"].ToString());
            ttvlcovat = double.Parse(dt.Rows[0]["ThanhTienVLCoVAT"].ToString());
            ttvlkvat = double.Parse(dt.Rows[0]["ThanhTienVLKhongVAT"].ToString());
            //cvccovat = double.Parse(dt.Rows[0]["GiaCuocCoVAT"].ToString());
            //cvckvt = double.Parse(dt.Rows[0]["GiaCuocKhongVAT"].ToString());

            ttcvccovat = double.Parse(dt.Rows[0]["ThanhTienCuocVCCoVAT"].ToString());
            ttcvckvat = double.Parse(dt.Rows[0]["ThanhTienCuocVCKhongVAT"].ToString());
            tt1m3covat = double.Parse(dt.Rows[0]["ThanhTien1m3CoVAT"].ToString());
            tt1m3kvat = double.Parse(dt.Rows[0]["ThanhTien1m3KhongVAT"].ToString());
            tt1chuyencovat = double.Parse(dt.Rows[0]["ThanhTien1ChuyenCoVAT"].ToString());
            tt1chuyenkvat = double.Parse(dt.Rows[0]["ThanhTien1ChuyenKhongVAT"].ToString());
        }
        if (GV.Rows.Count > 0)
        {
            GV.FooterRow.Cells[3].Text = "Tổng cộng";
            GV.FooterRow.Cells[3].HorizontalAlign = HorizontalAlign.Center;
            GV.FooterRow.Cells[6].Text = ConvertDecimal(klmuaVAT);
            GV.FooterRow.Cells[7].Text = ConvertDecimal(klmuaKVAT);
            GV.FooterRow.Cells[8].Text = ConvertDecimal(klthucte);
            GV.FooterRow.Cells[9].Text = ConvertDecimal(tlhang);
            GV.FooterRow.Cells[10].Text = ConvertDecimal(klsauquidoi);
            //GV.FooterRow.Cells[11].Text = ConvertDecimal(giavlvat);
            //GV.FooterRow.Cells[12].Text = ConvertDecimal(giavlkhongvat);
            GV.FooterRow.Cells[13].Text = ConvertDecimal(ttvlcovat);
            GV.FooterRow.Cells[14].Text = ConvertDecimal(ttvlkvat);
            //GV.FooterRow.Cells[15].Text = ConvertDecimal(cvccovat);
            //GV.FooterRow.Cells[16].Text = ConvertDecimal(cvckvt);
            GV.FooterRow.Cells[17].Text = ConvertDecimal(ttcvccovat);
            GV.FooterRow.Cells[18].Text = ConvertDecimal(ttcvckvat);

            GV.FooterRow.Cells[19].Text = ConvertDecimal(tt1m3covat);
            GV.FooterRow.Cells[20].Text = ConvertDecimal(tt1m3kvat);
            GV.FooterRow.Cells[21].Text = ConvertDecimal(tt1chuyencovat);
            GV.FooterRow.Cells[22].Text = ConvertDecimal(tt1chuyenkvat);
        }
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;

            if (dlBenBan.SelectedIndex > 0 && dlBenChayCuoc.SelectedIndex.Equals(0))
                SearchByBenBan(CurrentPage);
            else if (dlBenBan.SelectedIndex > 0 && dlBenChayCuoc.SelectedIndex > 0)
                SearchByBenBanBenChayCuoc(CurrentPage);
            else
                SearchAll(CurrentPage);

            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }

    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;
        if (dlBenBan.SelectedIndex > 0 && dlBenChayCuoc.SelectedIndex.Equals(0))
            SearchByBenBan(CurrentPage);
        else if (dlBenBan.SelectedIndex > 0 && dlBenChayCuoc.SelectedIndex > 0)
            SearchByBenBanBenChayCuoc(CurrentPage);
        else
            SearchAll(CurrentPage);

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;


    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        if (dlBenBan.SelectedIndex > 0 && dlBenChayCuoc.SelectedIndex.Equals(0))
            SearchByBenBan(CurrentPage);
        else if (dlBenBan.SelectedIndex > 0 && dlBenChayCuoc.SelectedIndex > 0)
            SearchByBenBanBenChayCuoc(CurrentPage);
        else
            SearchAll(CurrentPage);

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }
    string ConvertDecimal(double s)
    {
        string q = "";
        if (s.ToString().Contains("."))
        {
            double x = double.Parse(s.ToString().Substring(0, s.ToString().IndexOf('.')));
            q = (x != 0 && x > 999) ? string.Format("{0:0,0}", x) : x.ToString();
            q += s.ToString().Substring(s.ToString().IndexOf('.'));
        }
        else
            q = (s != 0 && s > 999) ? string.Format("{0:0,0}", s) : s.ToString();
        return q;
    }
    protected void dlLoaiBaoCao_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiBaoCao.SelectedIndex.Equals(0))
            Response.Redirect("BCNHTongHop.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(1))
            Response.Redirect("BCNHBenChayCuoc.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(2))
            Response.Redirect("BCNHBenBan.aspx");
        else
            if (dlLoaiBaoCao.SelectedIndex.Equals(3))
                Response.Redirect("BCNHBienSoXe.aspx");
            else if (dlLoaiBaoCao.SelectedIndex.Equals(4))
                Response.Redirect("BCNHLoaiVL.aspx");
    }

    protected void dlBenBan_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlBenBan.SelectedIndex > 0)
        {
            getDate();
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@BenBan", ex.GetGuid(dlBenBan.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlBenChayCuoc.Items.Clear();
            dlBenChayCuoc.DataSource = ex.GetData("sp_BCNH_LoadBenChayCuocByBenBan", p);
            dlBenChayCuoc.DataBind();
            dlBenChayCuoc.Items.Insert(0, new ListItem("--Chọn bên chạy cước--", ""));
            dlBenChayCuoc.SelectedIndex = 0;
        }
        else
            LoadbenChayCuoc(dlBenChayCuoc);
    }
}